curl -L -o ToeiBus-GTFS.zip https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip

unzip ToeiBus-GTFS.zip

# ls -1
ToeiBus-GTFS.zip
agency.txt: 運行会社情報
agency_jp.txt
attributions.txt
calendar.txt: 運行日情報
calendar_dates.txt: 運行日付情報
fare_attributes.txt: 運賃情報
fare_rules.txt: 運賃ルール情報
feed_info.txt: フィード情報
office_jp.txt
routes.txt: 路線情報
shapes.txt: 路線形状情報
stop_times.txt: 時刻表情報
stops.txt: 停留所情報
translations.txt
trips.txt: 便情報

$ duckdb ViewGTFS.duckdb

v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D 

INSTALL spatial;
LOAD spatial;

-- stops.csvをstopsテーブルに保存する
CREATE OR REPLACE TABLE stops AS SELECT * FROM read_csv('stops.txt');
CREATE OR REPLACE TABLE shapes AS SELECT * FROM read_csv('shapes.txt');
CREATE OR REPLACE TABLE trips AS SELECT * FROM read_csv('trips.txt');

SELECT * FROM stops LIMIT 5;

CREATE OR REPLACE TABLE stop_times AS SELECT * FROM read_csv(
    'stop_times.txt', 
    header=true, 
    columns={ 
        'trip_id': 'VARCHAR', 
        'arrival_time': 'VARCHAR', 
        'departure_time': 'VARCHAR', 
        'stop_id': 'VARCHAR', 
        'stop_sequence': 'INTEGER', 
        'stop_headsign': 'VARCHAR', 
        'pickup_type': 'UTINYINT', 
        'drop_off_type': 'UTINYINT', 
        'shape_dist_traveled': 'DOUBLE', 
        'timepoint': 'UTINYINT' 
});

# Usage: bash GTFS2DuckDB.sh GTFS_URL
# bash GTFS2DuckDB.sh https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip

TITLE=$1
GTFS_URL=$2

GTFS_FILE=${GTFS_URL##*/}

# GTFSファイルをダウンロード
echo "Download: $GTFS_FILE"
curl -L -o $TITLE.zip $GTFS_URL

echo "Import: $GTFS_FILE to $TITLE.duckdb"
for basename in `unzip -Z1 $TITLE.zip`; do
    filename=${basename%.*}
    echo "${filename} to DuckDB"
    # stop_timesのみスキーマ自動判定が失敗するので、手動でスキーマ定義を渡す（arrival_time, departure_timeが25:00:00のような24時越えの値を含むため）
    if [ $filename = "stop_times" ]; then
        unzip -p $TITLE.zip $basename | duckdb $TITLE.duckdb -c "CREATE OR REPLACE TABLE ${filename} AS SELECT * FROM read_csv('/dev/stdin', header=true, columns={ 'trip_id': 'VARCHAR', 'arrival_time': 'VARCHAR', 'departure_time': 'VARCHAR', 'stop_id': 'VARCHAR', 'stop_sequence': 'INTEGER', 'stop_headsign': 'VARCHAR', 'pickup_type': 'UTINYINT', 'drop_off_type': 'UTINYINT', 'shape_dist_traveled': 'DOUBLE', 'timepoint': 'UTINYINT' })"
    else
        unzip -p $TITLE.zip $basename | duckdb $TITLE.duckdb -c "CREATE OR REPLACE TABLE ${filename} AS SELECT * FROM read_csv('/dev/stdin')"
    fi
done

bash GTFS2DuckDB.sh https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip

$ unzip QDDSWQ5339.zip
$ duckdb ViewGTFS.duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D load spatial;
D CREATE OR REPLACE TABLE mesh_5th AS SELECT * FROM ST_Read('MESH05339.shp');

$ unzip -p tblT001142Q5339.zip | iconv -c -f cp932 -t utf-8 > tblT001142Q5339_utf8.txt

$ duckdb ViewGTFS.duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D load spatial;
-- 2行の日本語ヘッダーがレコードとして含まれているが気にしない
D CREATE OR REPLACE TABLE census AS SELECT * FROM read_csv('tblT001142Q5339_utf8.txt', header = true);

-- メッシュ内総人口はT001142001カラムに保存されているので、メッシュコードのポリゴンと人口をJOINしてGeoJSONとして保存する
D COPY (
    SELECT mesh_5th.KEY_CODE, census.T001142001 AS population, geom FROM mesh_5th 
    LEFT JOIN census ON mesh_5th.KEY_CODE = census.KEY_CODE
) TO 'census.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');